
Partial Class SAD_SQSearch
    Inherits System.Web.UI.Page

    Private Const Q As String = Chr(34)

    Private moTables As ArrayList = Nothing
    Private moViews As ArrayList = Nothing
    Private moProcs As ArrayList = Nothing
    Private moFunctions As ArrayList = Nothing

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            '---Load servers

            cmbServer.Items.Clear()
            cmbServer.Items.Add(New ListItem("All Servers", SQLAutoDocLib.ObjectBase.NODATA))

            Dim oServerFactory As New SQLAutoDocLib.Server_Factory
            For Each oServer As SQLAutoDocLib.Server In oServerFactory.AllActiveServers
                cmbServer.Items.Add(New ListItem(oServer.Name, oServer.ID))
            Next

            hidSID.Value = cmbServer.Value

            cmbDatabase.Items.Clear()
            cmbDatabase.Items.Add(New ListItem("All Databases", SQLAutoDocLib.ObjectBase.NODATA))

        Else
            If hidSID.Value <> cmbServer.Value Then
                cmbDatabase.Items.Clear()
                cmbDatabase.Items.Add(New ListItem("All Databases", SQLAutoDocLib.ObjectBase.NODATA))

                If cmbServer.Value > -1 Then
                    Dim oDatabaseFactory As New SQLAutoDocLib.Database_Factory
                    For Each oDatabase As SQLAutoDocLib.Database In oDatabaseFactory.GetAllDatabasesForServer(cmbServer.Value)
                        cmbDatabase.Items.Add(New ListItem(oDatabase.Name, oDatabase.ID))
                    Next
                End If

                hidSID.Value = cmbServer.Value

            ElseIf Request.Form(cmdSearch.UniqueID) > "" Then
                '---Do search
                Dim lServerID As Long = cmbServer.Value
                Dim lDatabaseID As Long = cmbDatabase.Value
                Dim sStartDate As String = txtStartDate.Value
                Dim sEndDate As String = txtEndDate.Value

                Dim bIncludeTables As Boolean = chkTables.Checked
                Dim bIncludeColumns As Boolean = chkColumns.Checked
                Dim bIncludeViews As Boolean = chkViews.Checked
                Dim bIncludeProcs As Boolean = chkProcs.Checked
                Dim bIncludeFunctions As Boolean = chkfunctions.Checked

                Dim sSearchText As String = txtFindText.Value

                GetSearchResults(lServerID, lDatabaseID, sStartDate, sEndDate, bIncludeTables, bIncludeColumns, bIncludeViews, bIncludeProcs, bIncludeFunctions, sSearchText)

                tdFoundObjectLinks.InnerHtml = _ShowFoundObjects_Links()
                divSearchResults.InnerHtml = _ShowFoundObjects()
            End If
        End If
    End Sub

    Private Sub GetSearchResults( _
        ByVal lServerID As Long, _
        ByVal lDatabaseID As Long, _
        ByVal sStartDate As String, _
        ByVal sEndDate As String, _
        ByVal bIncludeTables As Boolean, _
        ByVal bIncludeColumns As Boolean, _
        ByVal bIncludeViews As Boolean, _
        ByVal bIncludeProcs As Boolean, _
        ByVal bIncludeFunctions As Boolean, _
        ByVal sSearchText As String)

        If bIncludeTables Then
            Dim oTableFactory As New SQLAutoDocLib.Table_Factory
            moTables = oTableFactory.FindTables(lServerID, lDatabaseID, sStartDate, sEndDate, sSearchText, bIncludeColumns)
        Else
            moTables = Nothing
        End If

        If bIncludeViews Then
            Dim oViewFactory As New SQLAutoDocLib.View_Factory
            moViews = oViewFactory.FindViews(lServerID, lDatabaseID, sStartDate, sEndDate, sSearchText)
        Else
            moViews = Nothing
        End If

        If bIncludeProcs Then
            Dim oSProcFactory As New SQLAutoDocLib.SProc_Factory
            moProcs = oSProcFactory.FindSProcs(lServerID, lDatabaseID, sStartDate, sEndDate, sSearchText)
        Else
            moProcs = Nothing
        End If

        If bIncludeFunctions Then
            Dim oFunctionFactory As New SQLAutoDocLib.Function_Factory
            moFunctions = oFunctionFactory.FindFunctions(lServerID, lDatabaseID, sStartDate, sEndDate, sSearchText)
        Else
            moFunctions = Nothing
        End If
    End Sub

    Private Function _ShowFoundObjects_Links() As String
        Dim oHTM As New StringBuilder

        With oHTM
            .Append("<table>")
            .Append("<tr>")

            .Append("<td style=" & Q & "width: 120px;" & Q & ">")
            If Not moTables Is Nothing AndAlso moTables.Count > 0 Then
                .Append("<a href=" & Q & "#Tables" & Q & ">Tables</a>")
            End If
            .Append("</td>")

            .Append("<td style=" & Q & "width: 120px;" & Q & ">")
            If Not moViews Is Nothing AndAlso moViews.Count > 0 Then
                .Append("<a href=" & Q & "#Views" & Q & ">Views</a>")
            End If
            .Append("</td>")

            .Append("<td style=" & Q & "width: 120px;" & Q & ">")
            If Not moProcs Is Nothing AndAlso moProcs.Count > 0 Then
                .Append("<a href=" & Q & "#Procedures" & Q & ">Procedures</a>")
            End If

            .Append("<td style=" & Q & "width: 120px;" & Q & ">")
            If Not moFunctions Is Nothing AndAlso moFunctions.Count > 0 Then
                .Append("<a href=" & Q & "#Functions" & Q & ">Functions</a>")
            End If
            .Append("</td>")

            .Append("</tr>")
            .Append("</table>")
        End With

        Return oHTM.ToString
    End Function

    Private Function _ShowFoundObjects() As String
        ' FIX: 2007-10-29 JCL Function search improperly referenced procedures collection.

        Dim sHTM As New StringBuilder

        If Not moTables Is Nothing AndAlso moTables.Count > 0 Then sHTM.Append(_ShowObjectList("Tables", "SQTableD.aspx", moTables))
        If Not moViews Is Nothing AndAlso moViews.Count > 0 Then sHTM.Append(_ShowObjectList("Views", "SQViewD.aspx", moViews))
        If Not moProcs Is Nothing AndAlso moProcs.Count > 0 Then sHTM.Append(_ShowObjectList("Procedures", "SQSProcD.aspx", moProcs))
        If Not moFunctions Is Nothing AndAlso moFunctions.Count > 0 Then sHTM.Append(_ShowObjectList("Functions", "SQFunctionD.aspx", moFunctions))

        Return sHTM.ToString
    End Function

    Private Function _ShowObjectList( _
                    ByVal sTitle As String, _
                    ByVal sDetailURL As String, _
                    ByVal oList As ArrayList) As String

        Dim oHTM As New StringBuilder

        With oHTM
            .Append("<A Name=" & Q & sTitle & Q & "/>")

            .Append("<table class=" & Q & "wmObjectList_T" & Q & " style=" & Q & "border-collapse:collapse; height: 30px; border: solid 1px grey; width: 515px;" & Q & ">")

            .Append("<tr class=" & Q & "wmObjectList_TH" & Q & "  style=" & Q & "height: 30px; border-collapse: collapse; background-color: #C3C4C1; border-bottom: solid 1px black;" & Q & ">")
            .Append("<td colspan=" & Q & "4" & Q & " style=" & Q & "padding-left: 5px;" & Q & ">")
            .Append(sTitle)
            .Append("</td>")
            .Append("</tr>")

            .Append("<tr class=" & Q & "wmObjectList_TH" & Q & " style=" & Q & " background-color: #C3C4C1;" & Q & ">")

            .Append("<td class=" & Q & "wmObjectList_ZC" & Q & ">")
            .Append("&nbsp;")
            .Append("</td>")

            .Append("<td class=" & Q & "wmObjectList_ZM" & Q & ">")
            .Append("Modified")
            .Append("</td>")

            .Append("<td class=" & Q & "wmObjectList_ZR" & Q & ">")
            .Append("Current Rev")
            .Append("</td>")

            .Append("<td class=" & Q & "wmObjectList_ZN" & Q & ">")
            .Append("Name")
            .Append("</td>")

            .Append("</tr>")

            Dim bAlternatingRow As Boolean = False

            Dim sOwnerName As String = ""
            Dim sDatabaseName As String = ""
            Dim sServerName As String = ""

            Dim lObjectID As Long = SQLAutoDocLib.ObjectBase.NODATA

            Dim lOwnerID As Long = SQLAutoDocLib.Login.NODATA
            Dim lDatabaseID As Long = SQLAutoDocLib.Database.NODATA

            Dim bShowObject As Boolean = True

            Dim iDisplayedRows As Integer = 0

            For Each oObject As Object In oList
                bShowObject = True

                If chkDeletedRevs.Checked = False Then
                    If oObject.IsDeleted = True Then
                        bShowObject = False
                    End If
                End If

                If chkAllRevs.Checked = False Then
                    '---Only show the most recent rev of each object.
                    If lObjectID = oObject.id Then
                        bShowObject = False
                    Else
                        lObjectID = oObject.id
                    End If
                End If

                If bShowObject = True Then
                    iDisplayedRows = iDisplayedRows + 1

                    If lOwnerID <> oObject.OwnerID Then
                        lOwnerID = oObject.OwnerID
                        Dim oOwner As New SQLAutoDocLib.Login(lOwnerID)
                        oOwner.Load()
                        sOwnerName = oOwner.Login
                    End If

                    If lDatabaseID <> oObject.DatabaseID Then
                        lDatabaseID = oObject.DatabaseID
                        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
                        oDatabase.Load()
                        sDatabaseName = oDatabase.Name

                        Dim oServer As New SQLAutoDocLib.Server(oDatabase.ServerID)
                        oServer.Load()
                        sServerName = oServer.Name
                    End If

                    If bAlternatingRow = False Then
                        .Append("<tr class=" & Q & "wmObjectList_T" & Q & ">")
                    Else
                        .Append("<tr class=" & Q & "wmObjectList_TA" & Q & ">")
                    End If

                    .Append("<td>")
                    .Append("<a href=" & Q & sDetailURL & "?S=" & oObject.ID & "&D=" & oObject.DatabaseID & "&O=" & oObject.ID & Q & ">")
                    .Append("<img src=" & Q & "images/help.gif" & Q & " border=0/>")
                    .Append("</a>")

                    .Append("</td>")

                    .Append("<td>")
                    .Append(oObject.LastModified)
                    .Append("</td>")

                    .Append("<td>")
                    .Append(oObject.RevID)
                    .Append("</td>")

                    .Append("<td>")
                    If cmbServer.Value <> SQLAutoDocLib.Server.NODATA Then
                        If cmbDatabase.Value <> SQLAutoDocLib.Database.NODATA Then
                            .Append(sOwnerName & "." & oObject.Name)
                        Else
                            .Append(sDatabaseName & "." & sOwnerName & "." & oObject.Name)
                        End If
                    Else
                        .Append(sServerName & "." & sDatabaseName & "." & sOwnerName & "." & oObject.Name)
                    End If

                    .Append("</td>")

                    .Append("</tr>")

                    If oObject.Desc.Length > 0 Then
                        If bAlternatingRow = False Then
                            .Append("<tr class=" & Q & "wmObjectList_T" & Q & ">")
                        Else
                            .Append("<tr class=" & Q & "wmObjectList_TA" & Q & ">")
                        End If

                        .Append("<td>")
                        .Append("&nbsp;")
                        .Append("</td>")

                        .Append("<td>")
                        .Append("&nbsp;")
                        .Append("</td>")

                        .Append("<td colspan=" & Q & "2" & Q & ">")
                        .Append("<pre>")
                        .Append(oObject.Desc)
                        .Append("</pre>")
                        .Append("</td>")

                        .Append("</tr>")
                    End If

                    bAlternatingRow = Not bAlternatingRow
                End If
            Next

            If iDisplayedRows = 0 Then
                .Append("<tr class=" & Q & "wmObjectList_T" & Q & ">")
                .Append("<td colspan=" & Q & "4" & Q & " style=" & Q & "padding-bottom: 15px;" & Q & ">")
                .Append("No " & sTitle & " found that matched criteria.")
                .Append("</td>")
                .Append("</tr>")
            End If

            .Append("</table>")
        End With

        Return oHTM.ToString
    End Function
End Class
